<?php
class sis_sijaka_rollover_model extends MY_Model {
	var $model = 'sis_sijaka_rollover';
	
	function GetJSON($as_method = '')
	{
		$ls_sql	= "	SELECT MST.ID_SIJAKA,
						   AGT.ID_ANGGOTA,
					       SRT.NO_SERTIFIKAT,
					       PRS.FULL_NAME,
					       ROL.PERIODE_RO,
					       TO_CHAR (ROL.TGL_AWAL, 'DD/MM/YYYY') TGL_AWAL,
					       TO_CHAR (ROL.TGL_AKHIR, 'DD/MM/YYYY') TGL_AKHIR,
					       (CASE
					           WHEN NVL (MST.JENIS_RO, '1') = '1' THEN 'Nominal Saja'
					           ELSE 'Nominal & Margin'
					        END)
					          JENIS_RO,
					       MST.JENIS_RO CODE_RO,
					       SRT.JML_DANA,
					       ROL.JML_HARI,
					       ROL.SUKU_BUNGA,
					       ROL.JML_BUNGA,
					       ROL.PERSEN_PPH,
					       ROL.JML_PPH
					FROM    SIS_SIJAKA_MASTER MST
					        JOIN SIS_SIJAKA_SERTIFIKAT SRT ON MST.ID_SIJAKA = SRT.ID_SIJAKA
					        JOIN (SELECT ID_SIJAKA, NO_SERTIFIKAT, MAX(PERIODE_RO) PERIODE_RO FROM SIS_SIJAKA_HISTORIS_ROLLOVER GROUP BY ID_SIJAKA, NO_SERTIFIKAT) TMP_RO ON SRT.NO_SERTIFIKAT = TMP_RO.NO_SERTIFIKAT
					        JOIN SIS_SIJAKA_HISTORIS_ROLLOVER ROL ON TMP_RO.ID_SIJAKA = ROL.ID_SIJAKA AND TMP_RO.NO_SERTIFIKAT = ROL.NO_SERTIFIKAT AND TMP_RO.PERIODE_RO = ROL.PERIODE_RO
					        JOIN SIS_ANGGOTA AGT ON MST.ID_ANGGOTA = AGT.ID_ANGGOTA
					        JOIN SIS_SETUP_JENIS_ANGGOTA JNS ON JNS.KD_JENIS_ANGGOTA = AGT.KD_JENIS_ANGGOTA
					        JOIN SW_PERSON PRS ON PRS.PERSON_ID = AGT.PERSON_ID
 					WHERE 	UPPER(NVL(PRS.FULL_NAME,'')) LIKE '%".strtoupper(post('keyword'))."%'
 							OR UPPER(NVL(SRT.NO_SERTIFIKAT,'')) LIKE '%".strtoupper(post('keyword'))."%'
 							OR UPPER(NVL(AGT.ID_ANGGOTA,'')) LIKE '%".strtoupper(post('keyword'))."%'

 							AND MST.ST_APPROVE = '1'
        					AND NVL (MST.ST_TRANSFER, '0') = '1'
        					AND NVL (SRT.ST_AKTIF, '0') = '1' 
        					
							".(post('type_rollover') != '' ? " AND NVL(MST.JENIS_RO,'0') = '".post('type_rollover')."' " : '')."
							".(post('filter_id_anggota') != '' ? " AND NVL(AGT.ID_ANGGOTA,'0') = '".post('filter_id_anggota')."' " : '')."
							".(post('tgl_awal') != '' ? " AND ROL.TGL_AWAL >= TO_DATE('".post('tgl_awal')."','DD/MM/YYYY') " : '')."
							".(post('tgl_akhir') != '' ? " AND ROL.TGL_AKHIR <= TO_DATE('".post('tgl_akhir')."','DD/MM/YYYY') " : '')."
        					

            		ORDER BY ".(post('sort')=='' ? "NVL(AGT.ID_ANGGOTA,'0') ASC, SRT.NO_SERTIFIKAT" : post('sort'))." ".(post('order')=='' ? 'desc' : post('order'));
					
		return json_encode( $this->paging($ls_sql) );
	}
	
	function GetDetail($param=array())
	{
		
		$ls_sql	= "	SELECT	A.ID_SIJAKA, A.ID_ANGGOTA, TO_CHAR(A.TGL_PERMOHONAN,'DD/MM/YYYY') TGL_PERMOHONAN, A.JANGKA_WAKTU, A.JML_DANA, A.NOMINAL_SPLIT, A.SUKU_BUNGA, 
							A.JENIS_RO, A.PERIODE_BAYAR_BUNGA, A.SISTEM_BAYAR_BUNGA, A.SISTEM_SETOR_AWAL, A.BIAYA_MATERAI, 
							A.BIAYA_TRANSFER, TO_CHAR(A.TGL_SETOR,'DD/MM/YYYY') TGL_SETOR, TO_CHAR(A.TGL_CLOSE,'DD/MM/YYYY') TGL_CLOSE, 
							A.KD_BANK, A.NAMA_CABANG, A.NO_REKENING, A.PEMILIK_REKENING, 
							A.ST_APPROVE, F_SIS_STATUS_SIJAKA(A.ID_SIJAKA) STATUS_APPROVE, TO_CHAR(A.TGL_APPROVE,'DD/MM/YYYY') TGL_APPROVE, A.APPROVE_USER, 
							A.ST_TRANSFER, TO_CHAR(A.TGL_TRANSFER,'DD/MM/YYYY') TGL_TRANSFER, A.BUKTI_TRANSFER
					FROM	SIS_SIJAKA_MASTER A
							LEFT JOIN SIS_ANGGOTA B ON A.ID_ANGGOTA = B.ID_ANGGOTA
							LEFT JOIN SW_PERSON C ON B.PERSON_ID = C.PERSON_ID
							LEFT JOIN SIS_SETUP_JENIS_ANGGOTA D ON B.KD_JENIS_ANGGOTA = D.KD_JENIS_ANGGOTA
							LEFT JOIN KEU_BANK E ON E.KD_BANK = A.KD_BANK
					WHERE	(UPPER(NVL(A.ID_SIJAKA,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(A.ID_ANGGOTA,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(C.FULL_NAME,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(C.IDENTITY_NUMBER,'')) LIKE '%".strtoupper(post('keyword'))."%') 
							".(post('kd_jenis_anggota') != '' ? " AND B.KD_JENIS_ANGGOTA = '".post('kd_jenis_anggota')."' " : '')."
							".($as_method == 'NEW' ? " AND NVL(A.ST_APPROVE,'0') IN ('0') " : '')."
							".($as_method == 'APPROVAL' ? " AND NVL(A.ST_APPROVE,'0') IN ('0','1') AND NVL(A.ST_TRANSFER,'0') = '0' " : '')."
					ORDER BY NVL(A.ST_APPROVE,'0') ASC, A.TGL_PERMOHONAN";

		$result = $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			return $result->result_array();
		}else{
			return NULL;
		}
	}
	
	function Add($arr_data){
		$this->db->insert('sis_sijaka_master', $arr_data);
	}
	
	function Update($arr_data, $arr_rule){
		$this->db->update('sis_sijaka_master', $arr_data, $arr_rule);
	}
	
	function GetSingle($as_idpinjaman){
		$result = $this->db->get_where('sis_sijaka_master', array('ID_SIJAKA' => $as_idpinjaman));
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}
	
	function GetSingleView($as_idpinjaman)
	{
		$result = $this->db->query("SELECT	A.ID_SIJAKA, A.ID_ANGGOTA, TO_CHAR(A.TGL_PERMOHONAN,'DD/MM/YYYY') TGL_PERMOHONAN, A.JANGKA_WAKTU, A.JML_DANA, A.NOMINAL_SPLIT, A.SUKU_BUNGA, 
											A.JENIS_RO, A.PERIODE_BAYAR_BUNGA, A.SISTEM_BAYAR_BUNGA, A.SISTEM_SETOR_AWAL, A.BIAYA_MATERAI, 
											A.BIAYA_TRANSFER, TO_CHAR(A.TGL_SETOR,'DD/MM/YYYY') TGL_SETOR, TO_CHAR(A.TGL_CLOSE,'DD/MM/YYYY') TGL_CLOSE, 
											A.KD_BANK, A.NAMA_CABANG, A.NO_REKENING, A.PEMILIK_REKENING, 
											A.ST_APPROVE, F_SIS_STATUS_SIJAKA(A.ID_SIJAKA) STATUS_APPROVE, TO_CHAR(A.TGL_APPROVE,'DD/MM/YYYY') TGL_APPROVE, A.APPROVE_USER, 
											A.ST_TRANSFER, TO_CHAR(A.TGL_TRANSFER,'DD/MM/YYYY') TGL_TRANSFER, A.BUKTI_TRANSFER,
											C.FULL_NAME, D.NAMA_JENIS_ANGGOTA, E.NAMA_BANK
									FROM	SIS_SIJAKA_MASTER A
											LEFT JOIN SIS_ANGGOTA B ON A.ID_ANGGOTA = B.ID_ANGGOTA
											LEFT JOIN SW_PERSON C ON B.PERSON_ID = C.PERSON_ID
											LEFT JOIN SIS_SETUP_JENIS_ANGGOTA D ON B.KD_JENIS_ANGGOTA = D.KD_JENIS_ANGGOTA
											LEFT JOIN KEU_BANK E ON E.KD_BANK = A.KD_BANK
									WHERE	A.ID_SIJAKA = '".$as_idpinjaman."'");
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}

	function DeleteSingle($arr_rule)
	{
		try {
			if(	intval($this->get_data("SELECT COUNT(1) FROM SIS_SIJAKA_MASTER WHERE ID_SIJAKA = '".$arr_rule['id_sijaka']."' AND NVL(ST_APPROVE,'0') <> '0'"))>0)
			{
				return $this->error_delete.' [id: '.$arr_rule['id_sijaka']."]<br />";
			}else{
				$this->db->delete('sis_sijaka_historis_bunga', array('ID_SIJAKA' => $arr_rule['id_sijaka']));
				$this->db->delete('sis_sijaka_historis_rollover', array('ID_SIJAKA' => $arr_rule['id_sijaka']));
				$this->db->delete('sis_sijaka_sertifikat', array('ID_SIJAKA' => $arr_rule['id_sijaka']));
				$this->db->delete('sis_sijaka_master', array('ID_SIJAKA' => $arr_rule['id_sijaka']));
				return '';
			}
		} catch(Exception $e) {
			return $e->getMessage();
		}
	}
}
?>